﻿Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.Common
Imports System.IO

Public Class DBManager
    'This class is to manage all the database functions

    'Connection Strings
    'Private Shared strConnectionID As String = "Data Source=.\sqlm;Initial Catalog=goodAnotDB;Integrated Security=True"
    'Private Shared strConnectionID As String = "Data Source= JOLENE-PC\;Initial Catalog=goodAnotDB;Integrated Security=True"
    Private Shared strConnectionID As String = "Data Source= Yijun-PC\;Initial Catalog=goodAnotDB;Integrated Security=True"


#Region "Authentication and Authorization module"

    'Login using email and password
    Public Function login(ByVal email As String, ByVal password As String) As Boolean

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select * from user_account where email = @email and password = @password"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@email", email)
            sqlCmd.Parameters.AddWithValue("@password", password)

            dr = sqlCmd.ExecuteReader

            'If there is rows returned, it means that login successful
            If dr.HasRows Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'If login successful, get user details
    Public Function getUserDetails(ByVal email As String) As userAcc
        Dim userLoggedIn As userAcc = Nothing
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select user_ID, display_name, verified, is_admin, date_created from user_account where email = @email"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@email", email)
            dr = sqlCmd.ExecuteReader

            'Retrieving data
            While dr.Read()
                Dim userID = Convert.ToInt32(dr(0).ToString())
                Dim displayName = dr(1).ToString()
                Dim verified = Convert.ToBoolean(dr(2).ToString())
                Dim isAdmin = Convert.ToBoolean(dr(3).ToString())
                Dim dateCreated = Convert.ToDateTime(dr(4).ToString()).Date
                userLoggedIn = New userAcc(userID, displayName, verified, isAdmin, dateCreated)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return userLoggedIn



    End Function

    'Get the number of reviews from specified user
    Public Function getNoReviews(ByVal userID As Integer) As Integer
        Dim numReviews As Integer
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select reviews_no from user_account where user_ID = @userID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@user_ID", userID)

            dr = sqlCmd.ExecuteReader

            'Retrieving data
            While dr.Read()
                numReviews = Convert.ToInt32(dr(0).ToString())
            End While

            sqlConn.Close()
        Catch ex As Exception
            'IF exception occured, set as -1
            numReviews = -1
        End Try

        'True = no errors
        'False = exceptions occured
        Return numReviews
    End Function

    'Check verification code
    Public Function verify(ByVal userID As Integer, ByVal verificationCode As String) As Boolean
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select * from user_validation where user_ID = @userID and validation_code = @validationCode"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@userID", userID)
            sqlCmd.Parameters.AddWithValue("@validationCode", verificationCode)
            dr = sqlCmd.ExecuteReader
            'If there is rows returned, it means that login successful
            If dr.HasRows Then
                result = True

                'If successful, call verifyUpdate method
                verifyUpdate(userID)
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'If verification succeeds, update user_account table and delete verification code from user_validation table
    Public Sub verifyUpdate(ByVal userID As Integer)
        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'First query to update user details
            sqlCmd.CommandText = "Update user_account set verified = @true where user_ID = @userID"
            sqlCmd.Parameters.AddWithValue("@true", "True")
            sqlCmd.Parameters.AddWithValue("@userID", userID)
            sqlCmd.ExecuteNonQuery()

            'Second query to delete from validation
            sqlCmd.CommandText = "Delete from user_validation where user_ID = @userID"
            sqlCmd.ExecuteNonQuery()
            sqlConn.Close()

        Catch ex As Exception
        End Try

    End Sub
#End Region

#Region "User module"

    'Add user
    Public Function addUser(ByVal email As String, ByVal password As String, ByVal displayName As String, ByVal gender As Char) As Integer

        Dim result As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into user_account( email , password , display_name, gender, verified, is_admin, date_created, reviews_no) Values ( @email , @password , @display_name, @gender, @verified, @is_admin, @date_created, @reviews_no); SELECT SCOPE_IDENTITY()"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@email", email.ToLower())
            sqlCmd.Parameters.AddWithValue("@password", password)
            sqlCmd.Parameters.AddWithValue("@display_name", displayName)
            sqlCmd.Parameters.AddWithValue("@gender", gender)
            sqlCmd.Parameters.AddWithValue("@verified", "false")
            sqlCmd.Parameters.AddWithValue("@is_admin", "false")
            sqlCmd.Parameters.AddWithValue("@date_created", Date.Today.Date)
            sqlCmd.Parameters.AddWithValue("@reviews_no", 0)
            result = sqlCmd.ExecuteScalar
            sqlConn.Close()
        Catch ex As Exception
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Retrieve user
    Public Function retrieveUser(ByVal display_name As String) As userAcc
        Dim user As userAcc = Nothing
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Select email, password, first_name, last_name, gender, date_of_birth, country, verified, date_created from user_account where display_name= @display_name"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@display_name", display_name)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                Dim email = dr("email")
                Dim password = dr("password")
                Dim first_name = dr("first_name").ToString()
                Dim last_name = dr("last_name").ToString()
                Dim gender = dr("gender")
                Dim date_of_birth As Date
                Try
                    date_of_birth = dr("date_of_birth")
                Catch ex As Exception
                    date_of_birth = "01/01/0001"
                End Try

                Dim country = dr("country").ToString()
                Dim verified = dr("verified")
                Dim date_created = dr("date_created")
                user = New userAcc(display_name, password, verified, date_created, first_name, last_name, gender, email, date_of_birth, country)
            End While
            sqlConn.Close()
        Catch ex As Exception

        End Try
        'True = no errors
        'False = exceptions occured
        Return user
    End Function

    'Delete User
    Public Function deleteUser(ByVal display_name)
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete from user_account where display_name = @display_name"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@display_name", display_name)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Update User
    Public Function updateUser(ByVal email As String, ByVal display_name As String, ByVal first_name As String, ByVal last_name As String, ByVal date_of_birth As Date, ByVal country As String) As Boolean

        Dim dob As String
        If (date_of_birth.Date.ToString = "1/1/0001 AM 12:00:00") Then
            dob = " "
        Else
            dob = date_of_birth.Date.ToString
        End If

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update user_account set display_name = @display_name, first_name = @first_name, last_name = @last_name, date_of_birth= @date_of_birth , country=@country, verified=@verified, is_admin=@is_admin where email = @email"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@email", email.ToLower())
            sqlCmd.Parameters.AddWithValue("@display_name", display_name)
            sqlCmd.Parameters.AddWithValue("@first_name", first_name)
            sqlCmd.Parameters.AddWithValue("@last_name", last_name)
            sqlCmd.Parameters.AddWithValue("@date_of_birth", date_of_birth)
            sqlCmd.Parameters.AddWithValue("@country", country)
            sqlCmd.Parameters.AddWithValue("@verified", "false")
            sqlCmd.Parameters.AddWithValue("@is_admin", "false")
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Change Password
    Public Function changePassword(ByVal password As String)

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update user_account set password = @password where display_name = @display_name"""

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@password", password)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Check if email already exists
    Public Function checkAvailEmail(ByVal email As String)

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select * from user_account where email = @email"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@email", email)
            dr = sqlCmd.ExecuteReader
            'If there is rows returned, it means that name already exists
            If dr.HasRows Then
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        Return result
        'True = available
        'False = unavailable
    End Function

    'Check if display name already exists
    Public Function checkAvailDisplayName(ByVal displayName As String)

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select * from user_account where display_name = @displayName"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@displayName", displayName)
            dr = sqlCmd.ExecuteReader
            'If there is rows returned, it means that name already exists
            If dr.HasRows Then
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = available
        'False = unavailable
        Return result
    End Function

    'Add report to admin dashboard
    Public Function addReportDashboard(ByVal priority As Integer, ByVal type As String, ByVal comment As String, ByVal ID As Integer, ByVal resolved As Integer)

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into dashboard_report( priority, type, comment, ID, resolved ) Values ( @priority, @type, @comment, @ID, @resolved )"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@priority", priority)
            sqlCmd.Parameters.AddWithValue("@type", type)
            sqlCmd.Parameters.AddWithValue("@comment", comment)
            sqlCmd.Parameters.AddWithValue("@ID", ID)
            sqlCmd.Parameters.AddWithValue("@resolved", 0)

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Email verification
    Public Function emailVerification(ByVal validation_code As String)

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into user_validation(validation_code, date) Values (@validation_code,@date )"

            'Assign parameters
            'sqlCmd.Parameters.AddWithValue("@user_ID", user_ID)
            sqlCmd.Parameters.AddWithValue("@validation_code", validation_code)
            sqlCmd.Parameters.AddWithValue("@date", Date.Today.Date)

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function


#End Region

#Region "Shop Module"

    ' Add business details
    Public Function addBusinessDetails(ByVal name As String, ByVal category As String, ByVal website As String, ByVal details As String, ByVal date_created As Date, ByVal visits As Integer) As Integer
        Dim businessID As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into Business(name ,category, website, details, date_created, visits) Values ( @name ,  @category, @website, @details, @date_created, @visits); SELECT SCOPE_IDENTITY()"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@name", name)
            sqlCmd.Parameters.AddWithValue("@category", category)
            sqlCmd.Parameters.AddWithValue("@website", website)
            sqlCmd.Parameters.AddWithValue("@details", details)
            sqlCmd.Parameters.AddWithValue("@date_created", Today.Date)
            sqlCmd.Parameters.AddWithValue("@visits", 0)
            businessID = sqlCmd.ExecuteScalar()
            sqlConn.Close()
        Catch ex As Exception
        End Try

        'True = no errors
        'False = exceptions occured
        Return businessID
    End Function

    ' Add outlet details
    Public Function addOutletDetails(ByVal business_ID As Integer, ByVal name As String, ByVal address As String, ByVal openingHours As String, ByVal remarks As String, ByVal priority As Integer, ByVal phone_no As String, ByVal date_created As Date) As Boolean

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into outlet(business_ID ,name , address , opening_hours, remarks, priority , phone_no, date_created) Values (@business_ID, @name , @address , @opening_hours, @remarks, @priority, @phone_no, @date_created)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@business_ID", business_ID)
            sqlCmd.Parameters.AddWithValue("@name", name)
            sqlCmd.Parameters.AddWithValue("@address", address)
            sqlCmd.Parameters.AddWithValue("@opening_hours", openingHours)
            sqlCmd.Parameters.AddWithValue("@remarks", remarks)
            sqlCmd.Parameters.AddWithValue("@priority", priority)
            sqlCmd.Parameters.AddWithValue("@phone_no", phone_no)
            sqlCmd.Parameters.AddWithValue("@date_created", Today.Date)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    '  Add Business Outlet Report 
    Public Function addOutletReport(ByVal priority As Integer, ByVal type As String, ByVal comment As String, ByVal ID As Integer, ByVal resolved As Integer) As Boolean

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into dashboard_report(priority, type, comment, ID, resolved) Values (@priority,@type, @comment, @ID, @resolved)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@priority", priority)
            sqlCmd.Parameters.AddWithValue("@type", type)
            sqlCmd.Parameters.AddWithValue("@comment", comment)
            sqlCmd.Parameters.AddWithValue("@ID", ID)
            sqlCmd.Parameters.AddWithValue("@resolved", resolved)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Get business details by outletID
    Public Function getBusinessDetails(ByVal outletID As Integer) As String()
        Dim result(3) As String
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT business.name, business.category, business.website, business.details FROM outlet INNER JOIN business ON outlet.business_ID = business.business_ID WHERE (outlet.outlet_ID = @outlet_ID) GROUP BY business.name, business.category, business.website, business.details"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Name
                result(0) = dr(0).ToString()
                'Category
                result(1) = dr(1).ToString()
                'Website
                result(2) = dr(2).ToString()
                'Details
                result(3) = dr(3).ToString()

            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get business details by businessID
    Public Function getBusinessDetailsBID(ByVal businessID As Integer) As String()
        Dim result(3) As String
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT business.name, business.category, business.website, business.details FROM business where business_ID=@business_ID"
            sqlCmd.Parameters.AddWithValue("@business_ID", businessID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Name
                result(0) = dr(0).ToString()
                'Category
                result(1) = dr(1).ToString()
                ' Website
                result(2) = dr(2).ToString()
                'Details
                result(3) = dr(3).ToString()

            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get outlet details
    Public Function getOutletDetails(ByVal outletID As Integer) As String()
        Dim result(4) As String
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT name, address, opening_hours, remarks, phone_no FROM outlet WHERE (outlet_ID = @outlet_ID)"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Name
                result(0) = dr(0).ToString()
                'Address
                result(1) = dr(1).ToString()
                'Opening hours
                result(2) = dr(2).ToString()
                'Remarks
                result(3) = dr(3).ToString()
                'Phone number
                result(4) = dr(4).ToString()

            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get list of outlets belonging to a business
    Public Function getListOutlets(ByVal outletID As Integer) As ArrayList
        Dim result = New ArrayList()
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            'Getting all the outlets with the same business_ID as itself
            sqlCmd.CommandText = "SELECT outlet_ID, name FROM outlet WHERE (business_ID = (SELECT business_ID FROM outlet AS outlet_1 WHERE (outlet_ID = @outlet_ID)))"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add each line as an list item to populate the dropdownlist, text = outlet name and value = outlet_ID
                Dim item As ListItem = New ListItem(dr(1).ToString(), Convert.ToInt32(dr(0).ToString()))
                result.Add(item)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get the list of outlets belonging to a business by businessID
    Public Function getListOutletsBID(ByVal businessID As Integer) As ArrayList
        Dim result = New ArrayList()
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            'Getting all the outlets with the same business_ID as itself
            sqlCmd.CommandText = "SELECT name ,address , opening_hours, remarks ,phone_no FROM outlet INNER JOIN business ON outlet.business_ID = business.business_ID WHERE (outlet.businessID = @business_ID) "
            sqlCmd.Parameters.AddWithValue("@business_ID", businessID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add each line as an list item to populate the dropdownlist, text = outlet name and value = outlet_ID

                dr(0).ToString()



                ' Dim item As ListItem = New ListItem(dr(1).ToString(), Convert.ToInt32(dr(0).ToString()))
                'result.Add(item)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'update business details for admin 
    Public Function updateBusinessDetails(ByVal name As String, ByVal category As String, ByVal website As String, ByVal last_name As String, ByVal details As String, ByVal date_created As Date) As Boolean

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert business set name = @name, category = @category, website = @website, details = @details, date_created=@date_created"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@name", name)
            sqlCmd.Parameters.AddWithValue("@category", category)
            sqlCmd.Parameters.AddWithValue("@website", website)
            sqlCmd.Parameters.AddWithValue("@details", details)
            sqlCmd.Parameters.AddWithValue("@date_created", Today.Date)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'update outlet details for admin 
    Public Function updateOutletDetails(ByVal outlet_ID As Integer, ByVal name As String, ByVal address As String, ByVal opening_hours As String, ByVal remarks As String, ByVal priority As Integer, ByVal phone_no As String, ByVal date_created As Date) As Boolean

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update outlet set name = @name, address = @address, opening_hours = @opening_hours, remarks = @remarks, priority =@priority,date_created=@date_created where outlet_ID = @outlet_ID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@name", name)
            sqlCmd.Parameters.AddWithValue("@address", address)
            sqlCmd.Parameters.AddWithValue("@opening_hours", opening_hours)
            sqlCmd.Parameters.AddWithValue("@remarks", remarks)
            sqlCmd.Parameters.AddWithValue("@priority", 2)
            sqlCmd.Parameters.AddWithValue("@date_created", Today.Date)
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outlet_ID)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'delete business details
    Public Function deleteBusinessDetails(ByVal businessID As Integer) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete FROM business WHERE (businessID = @businessID) "
            sqlCmd.Parameters.AddWithValue("@businessID", Convert.ToInt32(businessID))

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'delete outlet details
    Public Function deleteOutletDetails(ByVal outletID As Integer) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete FROM business WHERE (outletID = @outletID) "
            sqlCmd.Parameters.AddWithValue("@outletID", Convert.ToInt32(outletID))

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'get business name from outletID
    Public Function getBusinessName(ByVal outletID As Integer) As String
        Dim result As String = ""
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT business.name FROM outlet INNER JOIN business ON outlet.business_ID = business.business_ID WHERE (outlet.outlet_ID = @outlet_ID) GROUP BY business.name"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = dr(0).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'get business name from businessID
    Public Function getBusinessNameBID(ByVal businessID As Integer) As String
        Dim result As String = ""
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT business.name FROM business WHERE (business_ID = @business_ID)"
            sqlCmd.Parameters.AddWithValue("@business_ID", businessID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = dr(0).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    Public Function getBusinessID(ByVal outletID As Integer) As Integer
        Dim result As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT business_ID FROM outlet WHERE (outlet_ID = @outlet_ID)"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = dr(0).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    Public Function getOutletIDFromBusinessID(ByVal businessID As Integer) As Integer
        Dim result As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT outlet.outlet_ID FROM business INNER JOIN outlet ON business.business_ID = outlet.business_ID Where business.business_id = @business_ID"
            sqlCmd.Parameters.AddWithValue("@business_ID", businessID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = dr(0).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    Public Function getOutletIDFromComment(ByVal commentID As Integer) As Integer
        Dim result As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT review.outlet_ID FROM comment INNER JOIN review ON comment.review_ID = review.review_ID Where comment.comment_ID = @commentID"
            sqlCmd.Parameters.AddWithValue("@commentID", commentID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = dr(0).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    Public Function getOutletIDFromReview(ByVal reviewID As Integer) As Integer
        Dim result As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT outlet_ID from review where review_ID = @reviewID"
            sqlCmd.Parameters.AddWithValue("@reviewID", reviewID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = dr(0).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function
#End Region

#Region "Search Module"

    'Update search tracker to keep track of searches if user is logged on
    Public Function trackSearch(ByVal searchField As String, ByVal userID As Integer)

        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into search_tracker(date_searched, search_field, user_ID) Values (@dateSearched, @searchField, @userID )"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@dateSearched", DateTime.Now)
            sqlCmd.Parameters.AddWithValue("@searchfield", searchField)
            sqlCmd.Parameters.AddWithValue("@userID", userID)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()

        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result

    End Function

    'Update search tracker to keep track of searches anonymously
    Public Function trackSearch(ByVal searchField As String)

        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into search_tracker(date_searched, search_field) Values (@dateSearched, @searchField )"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@dateSearched", DateTime.Now)
            sqlCmd.Parameters.AddWithValue("@searchfield", searchField)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()

        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result

    End Function

    'get the top 8 searched strings
    Public Function topSearch() As ArrayList
        Dim result As ArrayList = New ArrayList
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT distinct top 8 search_field, count(search_field) as numSearch from search_tracker group by search_field order by numSearch desc"
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add search field to arraylist
                Dim searchField = dr(0).ToString()
                result.Add(searchField)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

#End Region

#Region "Review and Rating module"

    'Add helpfulness rating to a review
    Public Function addReviewRate(ByVal reviewID As Integer, ByVal userID As Integer, ByVal helpful As Integer) As Boolean
        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into review_rate(review_ID, user_ID, helpful) Values ( @review_ID, @user_ID, @helpful)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@review_ID", reviewID)
            sqlCmd.Parameters.AddWithValue("@user_ID", userID)
            sqlCmd.Parameters.AddWithValue("@helpful", helpful)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()

        Catch ex As Exception
            'Exception occurs if violating primary key constraint if the same user tries to review for the same review twice
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result

    End Function

    'Update review rating
    Public Function updateReviewRating(ByVal reviewID As Integer, ByVal helpfulness As Integer) As Boolean
        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update review set helpfulness = @helpful where review_ID = @reviewID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@helpful", helpfulness)
            sqlCmd.Parameters.AddWithValue("@reviewID", reviewID)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()

        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Add comment to a review
    Public Function addReviewComment(ByVal reviewID As Integer, ByVal userID As Integer, ByVal commentContent As String) As Boolean
        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into comment(review_ID, user_ID, date_created, comment_content) Values ( @review_ID, @user_ID, @date_created, @comment_content)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@review_ID", reviewID)
            sqlCmd.Parameters.AddWithValue("@user_ID", userID)
            sqlCmd.Parameters.AddWithValue("@date_created", DateTime.Now)
            sqlCmd.Parameters.AddWithValue("@comment_content", commentContent)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()

        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result

    End Function

    'Get data to represent the overall review chart
    Public Function getChartData(ByVal outletID As Integer) As String()
        Dim result(3) As String

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT AVG(review.rating_quality) AS Quality, AVG(review.rating_value) AS Value, AVG(review.rating_service) AS Service, AVG(review.rating_overall) AS Overall FROM outlet INNER JOIN review ON outlet.outlet_ID = review.outlet_ID AND outlet.outlet_ID = review.outlet_ID WHERE (outlet.outlet_ID = @outlet_ID)"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Quality
                result(0) = Convert.ToInt32(dr(0).ToString())
                'Service
                result(1) = Convert.ToInt32(dr(1).ToString())
                'Value
                result(2) = Convert.ToInt32(dr(2).ToString())
                'Overall
                result(3) = Convert.ToDouble(dr(3).ToString())
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get data to represent the rating distribution chart
    Public Function getDistriChartData(ByVal outletID As Integer) As String()
        Dim result(4) As String

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT COUNT(rating_overall) AS countRating FROM review WHERE (outlet_ID = @outlet_ID) AND (rating_overall = @rating)"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            sqlCmd.Parameters.Add(sqlCmd.CreateParameter).ParameterName = "@rating"

            For x = 1 To 5
                sqlCmd.Parameters.Item("@rating").Value = x
                dr = sqlCmd.ExecuteReader
                While dr.Read()
                    'Quality
                    result(x - 1) = Convert.ToInt32(dr(0).ToString())
                End While
                dr.Close()
            Next

            sqlConn.Close()

        Catch ex As Exception

        End Try
        Return result
    End Function


    'Get business and outlet names for page title
    Public Function getBusinessOutletName(ByVal outletID As Integer) As String
        Dim result As String = ""
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT business.name AS businessName, outlet.name AS outletName FROM outlet INNER JOIN business ON outlet.business_ID = business.business_ID WHERE (outlet.outlet_ID = @outlet_ID) GROUP BY business.name, outlet.name"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                Dim businessName = dr(0).ToString()
                Dim outletName = dr(1).ToString()
                'Join the two names together
                result = businessName + " - " + outletName

            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get number of reviews belonging to an outlet
    Public Function getNumReviews(ByVal outletID As Integer) As Integer
        Dim result As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT COUNT(*) AS numReview FROM review WHERE (outlet_ID = @outletID) "
            sqlCmd.Parameters.AddWithValue("@outletID", outletID)
            result = Convert.ToInt32(sqlCmd.ExecuteScalar)
            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get overall ratings of an outlet based on all its reviews
    Public Function getOverallRating(ByVal outletID As Integer) As Double
        Dim result As Double
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Dim reviewCount = 0
        Dim totalRating = 0.0
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT review.rating_overall  FROM review INNER JOIN outlet ON review.outlet_ID = outlet.outlet_ID WHERE outlet.outlet_ID = @outlet_ID"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                reviewCount = reviewCount + 1
                totalRating = totalRating + Convert.ToDouble(dr(0).ToString)
            End While
            result = totalRating / reviewCount
            sqlConn.Close()
        Catch ex As Exception
            Return -1
        End Try
        Return result
    End Function

    'Deletes a specified review
    Public Function deleteReview(ByVal reviewID As String) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete from review_rate where (review_ID = @review_ID);Delete FROM comment WHERE (review_ID = @review_ID); Delete FROM review WHERE (review_ID = @review_ID)"
            sqlCmd.Parameters.AddWithValue("@review_ID", Convert.ToInt32(reviewID))

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'Deletes a specified review
    Public Function deleteComment(ByVal commentID As String) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete FROM comment WHERE (comment_ID = @comment_ID) "
            sqlCmd.Parameters.AddWithValue("@comment_ID", Convert.ToInt32(commentID))

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'Add a new review
    Public Function addReview(ByVal outletID As Integer, ByVal userID As Integer, ByVal title As String, ByVal ratingQuality As Integer, ByVal ratingValue As Integer, ByVal ratingService As Integer, ByVal ratingOverall As Integer, ByVal ratingVerdict As String, ByVal reviewContent As String) As Boolean
        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into review(outlet_ID, reviewer_ID, title, rating_quality, rating_value, rating_service, rating_overall, rating_verdict, helpfulness, date_created, review_content) Values(@outlet_ID, @reviewer_ID, @title, @rating_quality, @rating_value, @rating_service, @rating_overall, @rating_verdict, @helpfulness, @date_created, @review_content)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            sqlCmd.Parameters.AddWithValue("@reviewer_ID", userID)
            sqlCmd.Parameters.AddWithValue("@title", title)
            sqlCmd.Parameters.AddWithValue("@rating_quality", ratingQuality)
            sqlCmd.Parameters.AddWithValue("@rating_value", ratingValue)
            sqlCmd.Parameters.AddWithValue("@rating_service", ratingService)
            sqlCmd.Parameters.AddWithValue("@rating_overall", ratingOverall)
            sqlCmd.Parameters.AddWithValue("@rating_verdict", ratingVerdict)
            sqlCmd.Parameters.AddWithValue("@helpfulness", 0)
            sqlCmd.Parameters.AddWithValue("@date_created", Date.Today.Date)
            sqlCmd.Parameters.AddWithValue("@review_content", reviewContent)

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()

        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result

    End Function

    'Get number of comments for a specific review
    Public Function getReviewNumComments(ByVal reviewID As Integer) As Integer
        Dim result As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader

        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT COUNT(comment.comment_ID) AS commentNum FROM review INNER JOIN comment ON review.review_ID = comment.review_ID where review.review_ID = @reviewID"
            sqlCmd.Parameters.AddWithValue("@reviewID", reviewID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = Convert.ToInt32(dr(0).ToString())
            End While
            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Removes a report for a particular comment if it has been deleted
    Public Function removeReportComment(ByVal commentID As Integer) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete from dashboard_report where type = @type and ID = @commentID"
            sqlCmd.Parameters.AddWithValue("@commentID", commentID)
            sqlCmd.Parameters.AddWithValue("@type", "report comment")

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'Removes a report for a particular comment if it has been deleted
    Public Function removeReportReview(ByVal reviewID As Integer) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete from dashboard_report where type = @type and ID = @reviewID"
            sqlCmd.Parameters.AddWithValue("@reviewID", reviewID)
            sqlCmd.Parameters.AddWithValue("@type", "report review")

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    Public Function retrieveReportDetails(ByVal reportID As Integer) As String()
        Dim result(3) As String

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT type, comment, ID, resolved from dashboard_report where (report_ID = @reportID)"
            sqlCmd.Parameters.AddWithValue("@reportID", reportID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Type
                result(0) = Convert.ToInt32(dr(0).ToString())
                'Comment
                result(1) = Convert.ToInt32(dr(1).ToString())
                'ID
                result(2) = Convert.ToInt32(dr(2).ToString())
                'Resolved
                result(3) = Convert.ToDouble(dr(3).ToString())
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function




#End Region

#Region "Image module"

    'Add image
    Public Function addImage(ByVal imageName As String, ByVal image As HttpPostedFile, ByVal imageLength As Integer) As Integer

        Dim imageID As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            Dim imgByte(imageLength) As Byte
            image.InputStream.Read(imgByte, 0, imageLength)

            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into image(image_name, image, date_created) Values ( @imageName, @image, @date_created); SELECT SCOPE_IDENTITY()"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageName", imageName)
            sqlCmd.Parameters.AddWithValue("@image", imgByte)
            sqlCmd.Parameters.AddWithValue("@date_created", Date.Today.Date)

            imageID = sqlCmd.ExecuteScalar()

            sqlConn.Close()
        Catch ex As Exception

        End Try

        'True = no errors
        'False = exceptions occured
        Return imageID
    End Function

    'Assign businessID to image
    Public Function assignImageBusiness(ByVal imageID As Integer, ByVal businessID As Integer)
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update image set business_ID = @businessID where image_ID = @imageID"
            sqlCmd.Parameters.AddWithValue("@businessID", businessID)
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'Get imageID of business
    Public Function getBusinessImage(ByVal businessID As Integer) As Integer
        Dim imageID As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Dim reviewCount = 0
        Dim totalRating = 0.0
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT imageID from image where business_ID = @businessID"
            sqlCmd.Parameters.AddWithValue("@businessID", businessID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                imageID = dr(0)
            End While
        Catch ex As Exception
            Return Nothing
        End Try
        Return imageID
    End Function

    'Get imageID of outlet
    Public Function getOutletImage(ByVal outletID As Integer) As Integer
        Dim imageID As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT image_ID from image where business_ID = ( Select business_ID from outlet where outlet_ID = @outletID)"
            sqlCmd.Parameters.AddWithValue("@outletID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                imageID = dr(0)
            End While
        Catch ex As Exception
            Return Nothing
        End Try
        Return imageID
    End Function

    'Get imageID of user profile
    Public Function getUserImage(ByVal outletID As Integer) As Integer
        Dim imageID As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT imageID from image where user_ID = @user_ID"
            sqlCmd.Parameters.AddWithValue("@outletID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                imageID = dr(0)
            End While
        Catch ex As Exception
            Return 0
        End Try
        Return imageID
    End Function

    'Get imageID of top 3 most popular businesses
    Public Function getTopPopularBusiness() As ArrayList
        Dim result As ArrayList = New ArrayList
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT TOP (3) image.image_ID, business.visits FROM business INNER JOIN image ON business.business_ID = image.business_ID ORDER BY business.visits DESC"
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add search field to arraylist
                Dim imageID = dr(0).ToString()
                result.Add(imageID)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get imageID of top 3 newest businesses
    Public Function getTopNewestBusiness() As ArrayList
        Dim result As ArrayList = New ArrayList
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT TOP (3) image.image_ID FROM business INNER JOIN image ON business.business_ID = image.business_ID ORDER BY business.date_created DESC"
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add search field to arraylist
                Dim imageID = dr(0).ToString()
                result.Add(imageID)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function


    Public Function getOutletImageID(ByVal imageID As Integer) As Integer
        Dim result As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT DISTINCT outlet.outlet_ID FROM business INNER JOIN outlet ON business.business_ID = outlet.business_ID INNER JOIN image ON business.business_ID = image.business_ID WHERE (image.image_ID = @imageID)"
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add search field to arraylist
                Dim outletID = Convert.ToInt32(dr(0).ToString())
                result = outletID
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

#End Region

End Class
